package com.eduction.modules.system.dao;

import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.eduction.common.dbutil.DbHelper;
import com.eduction.modules.system.entity.CourseRecord;

@Repository
public class CourseRecordRepository {

	@Autowired
	private DbHelper dbhelper;
	@Autowired
	private CourseRecordDao courseRecordDao;
	
	/**
	 * 保存表格
	 */
	public void saveCourseRecord(CourseRecord c){
		this.courseRecordDao.save(c);
	}
	/**
	 * 查看某个表格信息通过sign
	 */
	public CourseRecord queryCourseRecordById(String id,String sid,String eid) throws SQLException{
		String sql = "select a.p_id as pid,a.school_id as schoolId,a.kind as kind,a.reporting_time as reportingTime,a.teacher as teacher,a.subject as subject,"
				+" a.teaching_time as teachingTime,a.technical_title as technicalTitle,a.week_class_hours as weekClassHours,a.level as level,"
				+" a.general_comment as generalComment,a.class as classs,a.expert_id as expertId,a.sign as sign,a.teach_content as teachContent,"
				+" s.s_name as schoolName,e.s_name as expertName"
				+" from t_course_record as a left join t_school as s on a.school_id = s.p_id left join t_expert as e on a.expert_id = e.p_id where a.sign = ? and a.school_id = ? and a.expert_id = ?";
		return this.dbhelper.getBean(sql, CourseRecord.class, id,sid,eid);
	}

	/**
	 * 查看某个表格信息通过sign
	 */
	public CourseRecord queryCourseRecordById2(String id,String sid) throws SQLException{
		String sql = "select a.p_id as pid,a.school_id as schoolId,a.kind as kind,a.reporting_time as reportingTime,a.teacher as teacher,a.subject as subject,"
				+" a.teaching_time as teachingTime,a.technical_title as technicalTitle,a.week_class_hours as weekClassHours,a.level as level,"
				+" a.general_comment as generalComment,a.class as classs,a.expert_id as expertId,a.sign as sign,a.teach_content as teachContent,"
				+" s.s_name as schoolName,e.s_name as expertName"
				+" from t_course_record as a left join t_school as s on a.school_id = s.p_id left join t_expert as e on a.expert_id = e.p_id where a.sign = ? and a.school_id = ?";
		return this.dbhelper.getBean(sql, CourseRecord.class, id,sid);
	}
	
	/**
	 * 查询这个专家创建的所有表格信息
	 */
	public List<CourseRecord> queryCRsByExpert(String expertId) throws SQLException{
		String sql = "SELECT a.p_id as pid,s.s_name as schoolName,a.school_id as schoolId,a.sign as sign from t_course_record as a LEFT JOIN t_school as s on a.school_id = s.p_id where a.expert_id = ?";
		return this.dbhelper.getBeanList(sql, CourseRecord.class, expertId);
	}

	public CourseRecord queryCRsById(String id) {
		return courseRecordDao.findOne(id);
	}

    public void deleteCourseRecordMapState(String sign, String schoolId, String expertId) throws SQLException{
		String sql = "delete from t_course_record where sign = ? and school_id = ? and expert_id = ?";
		this.dbhelper.execute(sql,sign,schoolId,expertId);
    }
}
